package agenceVoyageVirtuel.server.base;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import agenceVoyageVirtuel.client.beans.EpoqueData;
import agenceVoyageVirtuel.client.beans.PackData;
import agenceVoyageVirtuel.client.beans.VoyageData;
import agenceVoyageVirtuel.server.DB_Conn;

public class DB_Pack extends DB_Conn {
	/**
	 * constructor - nothing to do
	 */
	public DB_Pack() {
		// nothing to do
	}
	
	private List<PackData> initPacks() {
		String query = "SELECT idPack, libPack, descriptionPack FROM t_pack;";

		// prepare for rpc transport
		List<PackData> packDatas = new ArrayList<PackData>();
		
		try {
			Connection connection = getConn();
			Statement select = connection.createStatement();
			ResultSet result = select.executeQuery(query);
			int i = 0;
			
			while (result.next()) {
				packDatas.add(new PackData());

				packDatas.get(i).setIdPack(result.getInt(1));
				packDatas.get(i).setLibPack(result.getString(2));
				packDatas.get(i).setDescriptionPack(result.getString(3));

				i++;
			}

			// clean up
			result.close();
			connection.close();
		} catch(Exception e) {
			System.err.println("Mysql Statement Error: " + query);
			e.printStackTrace();
		}

		// return datas
		return packDatas;
	}
	
	public List<PackData> getPacks() {
		// prepare for rpc transport
		List<PackData> packDatas = initPacks();
		
		for(PackData p : packDatas) {
			String query = "SELECT t_voyage.idVoyage, destination, reel, tarifVoyage, image, t_epoque.idEpoque, libEpoque"
				+ " FROM t_packs JOIN t_voyage, t_epoque"
				+ " WHERE idPack=" + p.getIdPack()
				+ " AND t_packs.idVoyage=t_voyage.idVoyage"
				+ " AND t_packs.idEpoque=t_epoque.idEpoque;";
			
			List<VoyageData> voyageDatas = new ArrayList<VoyageData>();
		
			try {
				Connection connection = getConn();
				Statement select = connection.createStatement();
				ResultSet result = select.executeQuery(query);
				int i = 0;
				
				while (result.next()) {
					voyageDatas.add(new VoyageData());
					voyageDatas.get(i).setIdVoyage(result.getInt(1));
					voyageDatas.get(i).setDestination(result.getString(2));
					voyageDatas.get(i).setReel(result.getBoolean(3));
					voyageDatas.get(i).setTarifQuotidien(result.getInt(4));
					voyageDatas.get(i).setNomImage(result.getString(5));
					//Epoque
					voyageDatas.get(i).setEpoque(new EpoqueData());
					voyageDatas.get(i).getEpoque().setIdEpoque(result.getInt(6));
					voyageDatas.get(i).getEpoque().setLibEpoque(result.getString(7));
					
					i++;
				}
	
				p.setVoyages(voyageDatas);
				
				// clean up
				result.close();
				connection.close();
			} catch(Exception e) {
				System.err.println("Mysql Statement Error: " + query);
				e.printStackTrace();
			}
		}
		
		// return datas
		return packDatas;
	}
}
